<?php
namespace App\Modules\Common\Service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class OutExcel{
    protected $spreadsheet = null; // phpExcel操作对象


    /**
     * 获取当前活动工作区
     *
     * @return
     */
    public function getSheet(){
        return $this->spreadsheet->getActiveSheet();
    }

    function __construct($filename = null){
        if (is_null($filename)) {
            $this->spreadsheet = $this->newSheet();
        }else{
            $this->spreadsheet = $this->loadTemplate($filename);
        }
    }

    private function newSheet(){
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getActiveSheet()
            ->getDefaultColumnDimension()
            ->setWidth(14); // 设置默认列宽
        return $spreadsheet;
    }
    
    private function loadTemplate($templateFile){
        return IOFactory::load($templateFile);
    }

	/**
     * 设置列宽
     *
     * @param string $colum
     * @param int $width
     * @return void
     */
    public function setColumnWith($colum, $width){
        $this->spreadsheet->getActiveSheet()
            ->getColumnDimension($colum)
            ->setWidth($width);
    }


    /**
     * 设置一个单元下拉选择
     *
     * @param string $cell 单元位置
     * @param string|array $list 选项值 
     * @return void
     * @todo 下拉选择符存在最长限制
     */
    public function addCellDrop($cell, $list){
        if (is_string($list)) {
            $listStr = $list ;
        }elseif (is_array($list) ) {
            $listStr = implode(',', $list);
        }else{
            return false;
        }
        $this->spreadsheet->getActiveSheet()
            ->getCell($cell)
            ->getDataValidation()
            ->setType(DataValidation::TYPE_LIST)
            ->setShowDropDown(true)
            ->setFormula1('"' . $listStr . '"');
    }

    /**
     * 从数组填充数据
     *
     * @param array $array
     * @return void
     */    
    public function formArray($array, $start='A1'){
        $this->spreadsheet->getActiveSheet()
            ->fromArray($array, null, $start);
    }

    /**
     * 从数组插入数据
     *
     * @param Array $array
     * @param String $start
     * @return void
     */
    public function insertFromArray($array, $start){
        $startRow = self::addr2Coordinate($start)[1];
        if (count($array) > 1) {
            $this->spreadsheet->getActiveSheet()
                ->insertNewRowBefore($startRow + 1, count($array) -1);
        }
        $this->spreadsheet->getActiveSheet()
            ->fromArray($array, null,$start);
    }
    /**
     * 数据合计
     *
     * @param String $cellAddr 写入地址
     * @param String $start 开始地址
     * @param String $end   结束地址
     * @return void
     */
    public function setTotal($cellAddr, $start, $end){
        $this->spreadsheet->getActiveSheet()
            ->setCellValue($cellAddr, "=SUM({$start}:{$end})");
    }

    /**
     * 列合计
     *
     * @param int|string $column
     * @param int $start
     * @param int $end
     * @param int $save
     * @return void
     */
    public function columnTotal($column, $start, $end ,$save){
        $this->setTotal(
            self::coord2Addr($column, $save),
            self::coord2Addr($column, $start),
            self::coord2Addr($column, $end)
        );
    }
    /**
     * 输出文件流
     *
     * @param string $filename
     * @param \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet
     * @return \Illuminate\Http\Response|\Illuminate\Contracts\Routing\ResponseFactory
     */
    public function outFile($filename ='data', $spreadsheet = null){
        $spreadsheet = is_null($spreadsheet) ? $this->spreadsheet: $spreadsheet;
        $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, null, [
            'Content-Disposition' => 'attachment;filename=' . urlencode($filename.'.xls'),
            'Access-Control-Expose-Headers' => 'Content-Disposition',
            'Content-Type' => 'text/csv;charset=utf-8',
        ]);
    }

    /**
     * 坐标转地址
     *
     * @param Int|String $column
     * @param Int $row
     * @return String
     */
    public static function coord2Addr($column, $row):String {
        $column = is_numeric($column) ? 
            self::index2Column($column - 1): strtoupper($column);
        return $column . $row;
    }

    /**
     * 地址转坐标
     *
     * @return void
     */
    public static function addr2Coordinate(String $addr): Array{
        $addr = strtoupper($addr);
        $ret = [];
        $column = '';
        for ($i=0; $i < strlen($addr); $i++) { 
            if (is_numeric($addr[$i])) break;
            $column[$i] = $addr[$i];
        }
        $ret[0] = self::column2Index($column);
        $ret[1] = substr($addr, $i);
        return $ret;
    }

    /**
     * 列地址转索引
     *
     * @param String $column
     * @return Int
     */
    public static function column2Index(String $column): Int{
        $column = strtoupper($column);
        if(strlen($column) == 1) return ord($column) - ord('A');
        $index = 0;
        for ($i=0; $i < strlen($column); $i++) { 
            $index += $index * 25 + (ord($column[$i]) - ord('A') + 1);
        }
        return $index - 1;
    }

    /**
     * 列索引转地址
     *
     * @param Int $i 列索引
     * @return String
     */
    public static function index2Column(Int $i): String{
        if ($i < 26) return chr($i + ord('A'));
        return self::index2Column( floor($i / 26) - 1 ) 
            . self::index2Column($i % 26);
    }
    
}
